package com.code.generator.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;

import com.code.generator.GeneratorException;
import com.code.generator.entity.ColumnsEntity;
import com.code.generator.entity.TableEntity;
import com.code.plugin.MessagesProperties;


/**
 * oracle数据库连接
 *
 *
 * @author 朱国军
 * @version 2015年3月13日 朱国军
 */
public class OracleConnection implements DataBaseConnection {

    @Override
    public Connection getConnection(String dbUser, String dbPassword, String dbDriver, String dbUrl) throws Exception {
        Connection conn = null;

        Class.forName(dbDriver).newInstance();
        Properties props = new Properties();
        props.put("remarksReporting", "true"); //$NON-NLS-1$ //$NON-NLS-2$
        props.put("user", dbUser); //$NON-NLS-1$
        props.put("password", dbPassword); //$NON-NLS-1$
        conn = DriverManager.getConnection(dbUrl, props);
        return conn;

    }


    @Override
    public List<ColumnsEntity> getColumnsEntity(Connection conn, String tableName, String dbUser) throws Exception {
        List<ColumnsEntity> columnsEntitys = new ArrayList<ColumnsEntity>();

        DatabaseMetaData databaseMetaData = null;
        ResultSet resultSet = null;
        ResultSet primarySet = null;
        try {
            databaseMetaData = conn.getMetaData();
            resultSet = databaseMetaData.getColumns(null, dbUser.toUpperCase(), tableName.toUpperCase(), "%"); //$NON-NLS-1$
            while (resultSet.next()) {
                ColumnsEntity columnsEntity = new ColumnsEntity();
                columnsEntity.setColumnName(resultSet.getString("COLUMN_NAME")); //$NON-NLS-1$
                columnsEntity.setColumnSize(resultSet.getInt("COLUMN_SIZE")); //$NON-NLS-1$
                columnsEntity.setRemark(resultSet.getString("REMARKS")); //$NON-NLS-1$
                columnsEntity.setTypeName(resultSet.getString("TYPE_NAME")); //$NON-NLS-1$
                columnsEntity.setIsNullAble(resultSet.getString("IS_NULLABLE")); //$NON-NLS-1$
                columnsEntity.setIsPrimary(false);
                columnsEntitys.add(columnsEntity);
            }
            primarySet = databaseMetaData.getPrimaryKeys(null, null, tableName);
            while (primarySet.next()) {
                String primaryKeyName = primarySet.getString("COLUMN_NAME"); //$NON-NLS-1$
                for (ColumnsEntity columnsEntity : columnsEntitys) {
                    if (StringUtils.equals(primaryKeyName, columnsEntity.getColumnName())) {
                        columnsEntity.setIsPrimary(true);
                        break;
                    }
                }
            }
        } catch (SQLException e) {
            throw new GeneratorException(MessagesProperties.exception_sql_excuese, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                    resultSet = null;
                }
            } catch (SQLException e) {
                throw new GeneratorException(MessagesProperties.exception_close_connection, e);
            }
        }
        return columnsEntitys;
    }


    @Override
    public TableEntity getTableEntity(Connection conn, String tableName) throws Exception {
        TableEntity tableEntity = new TableEntity();

        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            String sql = "select table_name,comments from user_tab_comments where table_name='" + tableName.toUpperCase() + "'"; //$NON-NLS-1$ //$NON-NLS-2$
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                String newTableName = resultSet.getString(1);
                if (StringUtils.equals(tableName.toUpperCase(), newTableName)) {
                    tableEntity.setTableName(newTableName);
                    tableEntity.setRemark(resultSet.getString(2));
                } else {
                    throw new GeneratorException(MessagesProperties.exception_table + tableName
                            + MessagesProperties.exception_do_not_find);
                }
            }
        } catch (SQLException e) {
            throw new GeneratorException(MessagesProperties.exception_sql_excuese, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                    resultSet = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
            } catch (SQLException e) {
                throw new GeneratorException(MessagesProperties.exception_close_connection, e);
            }
        }
        return tableEntity;
    }

}
